MAKE_LSQ_SPLINE
Overview
The MAKE_LSQ_SPLINE function constructs a smoothing B-spline that approximates noisy or scattered data by minimizing the least squares (LSQ) criterion. Unlike interpolating splines that pass exactly through every data point, least-squares splines provide a smooth curve that best fits the data while reducing the influence of noise and outliers.
A B-spline (basis spline) is a piecewise polynomial function defined by a set of knots—specific locations where polynomial segments join. The smoothness and flexibility of the spline are controlled by the spline degree (default is cubic, k = 3) and the placement of interior knots. B-splines serve as basis functions, meaning any spline of a given degree can be expressed as a linear combination of B-splines.
The resulting spline S(x) is computed as:
S(x) = \sum_{j} c_j B_j(x; t)where B_j(x; t) are the B-spline basis functions defined over the knot vector t, and the coefficients c_j are determined by minimizing the weighted sum of squared residuals:
\sum_{i} \left( w_i \times (S(x_i) - y_i) \right)^2Here, w_i are optional weights that allow emphasizing certain data points over others during fitting.
This implementation wraps the scipy.interpolate.make_lsq_spline function from the SciPy library. The knot vector must satisfy the Schoenberg-Whitney conditions, which ensure a unique solution exists. The knots and data points must be arranged such that there is a subset of data points x_j satisfying t_j < x_j < t_{j+k+1} for each interior knot. Typically, boundary knots are repeated (k+1) times to create a “clamped” spline that interpolates the endpoints. For more details, see the SciPy documentation and the source code on GitHub.
This example function is provided as-is without any representation of accuracy.
Excel Usage
=MAKE_LSQ_SPLINE(x, y, t, x_new, k, w)x(list[list], required): The x-coordinates of the data pointsy(list[list], required): The y-coordinates of the data pointst(list[list], required): The knot vectorx_new(list[list], required): The x-coordinates at which to evaluate the splinek(int, optional, default: 3): B-spline degreew(list[list], optional, default: null): Weights for LSQ fitting
Returns (list[list]): A 2D list of interpolated values, or error message (str) if invalid.
Examples
Example 1: Demo case 1
Inputs:
| x | y | t | x_new |
|---|---|---|---|
| 0 | 0 | 0 | 0.5 |
| 1 | 1 | 0 | 1.5 |
| 2 | 4 | 0 | 2.5 |
| 3 | 9 | 0 | |
| 4 | 16 | 2 | |
| 4 | |||
| 4 | |||
| 4 | |||
| 4 |
Excel formula:
=MAKE_LSQ_SPLINE({0;1;2;3;4}, {0;1;4;9;16}, {0;0;0;0;2;4;4;4;4}, {0.5;1.5;2.5})Expected output:
| Result |
|---|
| 0.25 |
| 2.25 |
| 6.25 |
Example 2: Demo case 2
Inputs:
| x | y | t | x_new | k |
|---|---|---|---|---|
| 0 | 1 | 0 | 0.5 | 2 |
| 1 | 2 | 0 | 1 | |
| 2 | 3 | 0 | 2 | |
| 3 | 4 | 1.5 | ||
| 3 | ||||
| 3 | ||||
| 3 |
Excel formula:
=MAKE_LSQ_SPLINE({0;1;2;3}, {1;2;3;4}, {0;0;0;1.5;3;3;3}, {0.5;1;2}, 2)Expected output:
| Result |
|---|
| 1.5 |
| 2 |
| 3 |
Example 3: Demo case 3
Inputs:
| x | y | t | x_new | k | w |
|---|---|---|---|---|---|
| 0 | 0 | 0 | 0.5 | 1 | 1 |
| 1 | 1 | 0 | 1.5 | 2 | |
| 2 | 2 | 1.5 | 2.5 | 2 | |
| 3 | 3 | 3 | 1 | ||
| 3 |
Excel formula:
=MAKE_LSQ_SPLINE({0;1;2;3}, {0;1;2;3}, {0;0;1.5;3;3}, {0.5;1.5;2.5}, 1, {1;2;2;1})Expected output:
| Result |
|---|
| 0.5 |
| 1.5 |
| 2.5 |
Example 4: Demo case 4
Inputs:
| x | y | t | x_new | k |
|---|---|---|---|---|
| 0 | 0.1 | 0 | 1 | 3 |
| 0.5 | 0.6 | 0 | 2 | |
| 1 | 1.1 | 0 | ||
| 1.5 | 1.4 | 0 | ||
| 2 | 2.1 | 1.5 | ||
| 2.5 | 2.6 | 3 | ||
| 3 | 3.1 | 3 | ||
| 3 | ||||
| 3 |
Excel formula:
=MAKE_LSQ_SPLINE({0;0.5;1;1.5;2;2.5;3}, {0.1;0.6;1.1;1.4;2.1;2.6;3.1}, {0;0;0;0;1.5;3;3;3;3}, {1;2}, 3)Expected output:
| Result |
|---|
| 1.039 |
| 2.039 |
Python Code
import math
import numpy as np
from scipy.interpolate import make_lsq_spline as scipy_make_lsq_spline
def make_lsq_spline(x, y, t, x_new, k=3, w=None):
"""
Compute LSQ-based fitting B-spline.
See: https://docs.scipy.org/doc/scipy/reference/generated/scipy.interpolate.make_lsq_spline.html
This example function is provided as-is without any representation of accuracy.
Args:
x (list[list]): The x-coordinates of the data points
y (list[list]): The y-coordinates of the data points
t (list[list]): The knot vector
x_new (list[list]): The x-coordinates at which to evaluate the spline
k (int, optional): B-spline degree Default is 3.
w (list[list], optional): Weights for LSQ fitting Default is None.
Returns:
list[list]: A 2D list of interpolated values, or error message (str) if invalid.
"""
def to2d(val):
"""Normalize input to 2D list format."""
return [[val]] if not isinstance(val, list) else val
def flatten(arr):
"""Flatten a 2D list to a 1D list."""
return [item for sublist in arr for item in sublist]
def validate_numeric_array(arr, name):
"""Validate that array contains only finite numeric values."""
for i, row in enumerate(arr):
if not isinstance(row, list):
return f"Invalid input: {name} must be a 2D list."
for j, val in enumerate(row):
if not isinstance(val, (int, float)):
return f"Invalid input: {name}[{i}][{j}] must be a number."
if math.isnan(val) or math.isinf(val):
return f"Invalid input: {name}[{i}][{j}] must be finite."
return None
def validate_int(value, name):
"""Validate integer parameter."""
if not isinstance(value, (int, float)):
return f"Invalid input: {name} must be an integer."
if isinstance(value, float) and not value.is_integer():
return f"Invalid input: {name} must be an integer."
int_val = int(value)
if math.isnan(int_val) or math.isinf(int_val):
return f"Invalid input: {name} must be finite."
return int_val
# Normalize inputs to 2D lists
x = to2d(x)
y = to2d(y)
t = to2d(t)
x_new = to2d(x_new)
# Validate numeric arrays
error = validate_numeric_array(x, "x")
if error:
return error
error = validate_numeric_array(y, "y")
if error:
return error
error = validate_numeric_array(t, "t")
if error:
return error
error = validate_numeric_array(x_new, "x_new")
if error:
return error
# Validate k parameter
validated_k = validate_int(k, "k")
if isinstance(validated_k, str):
return validated_k
k = validated_k
if k < 0:
return "Invalid input: k must be non-negative."
# Flatten 2D lists to 1D
x_flat = flatten(x)
y_flat = flatten(y)
t_flat = flatten(t)
x_new_flat = flatten(x_new)
# Validate array lengths
if len(x_flat) != len(y_flat):
return "Invalid input: x and y must have the same length."
if len(x_flat) == 0:
return "Invalid input: x and y must not be empty."
if len(t_flat) == 0:
return "Invalid input: t must not be empty."
if len(x_new_flat) == 0:
return "Invalid input: x_new must not be empty."
# Validate knot vector is non-decreasing
for i in range(len(t_flat) - 1):
if t_flat[i] > t_flat[i + 1]:
return "Invalid input: knot vector t must be non-decreasing."
# Validate knot vector has sufficient length
if len(t_flat) < k + 2:
return f"Invalid input: knot vector must have at least {k + 2} elements for degree {k} spline (got {len(t_flat)})."
# Process weights if provided
w_flat = None
if w is not None:
w = to2d(w)
error = validate_numeric_array(w, "w")
if error:
return error
w_flat = flatten(w)
if len(w_flat) != len(x_flat):
return "Invalid input: w must have the same length as x and y."
# Call scipy function
try:
x_array = np.array(x_flat, dtype=float)
y_array = np.array(y_flat, dtype=float)
t_array = np.array(t_flat, dtype=float)
x_new_array = np.array(x_new_flat, dtype=float)
w_array = np.array(w_flat, dtype=float) if w_flat is not None else None
spline = scipy_make_lsq_spline(x_array, y_array, t_array, k=k, w=w_array)
result = spline(x_new_array)
# Validate result
if not isinstance(result, np.ndarray):
return "scipy.interpolate.make_lsq_spline error: unexpected result type."
# Check for non-finite values in result
if not np.all(np.isfinite(result)):
return "scipy.interpolate.make_lsq_spline error: result contains non-finite values."
# Convert to 2D list (column vector)
return [[float(val)] for val in result]
except ValueError as e:
return f"Invalid input: {e}"
except Exception as e:
return f"scipy.interpolate.make_lsq_spline error: {e}"